//
//  AlbumMigration.swift
//  hello
//
//  Created by 赵康 on 2024/11/11.
//

import Fluent
import Foundation
import Vapor
import FluentSQL

extension Album {
	struct Migration: AsyncMigration {
		var name: String { "AlbumMigration" }
		
		func prepare(on database: any Database) async throws {
			try await database.schema(Album.schema)
				.id()
				.field(Album.FieldKeys.title, .string, .required)
				.field(Album.FieldKeys.description, .sql(.text))
				.field(Album.FieldKeys.cover, .string)
				.field(Album.FieldKeys.category, .string, .required)
				.field(Album.FieldKeys.audioCount, .int, .required, .custom("DEFAULT 0"))
				.field(Album.FieldKeys.playCount, .int, .required, .custom("DEFAULT 0"))
				.field(Album.FieldKeys.favoriteCount, .int, .required, .custom("DEFAULT 0"))
				.field(Album.FieldKeys.isDeleted, .bool, .required, .custom("DEFAULT FALSE"))
				.field(Album.FieldKeys.createdAt, .datetime, .required)
				.field(Album.FieldKeys.updatedAt, .datetime, .required)
				.create()
		}
		func revert(on database: any Database) async throws {
			try await database.schema(Album.schema).delete()
		}
	}
}

extension Album {
  struct AddWordCountField: AsyncMigration {
    var name: String  { "AddWordCountFields" }
    
      // 创建一个内部结构体来解码结果
    private struct CountResult: Codable {
      let count: Int
    }
    
    func prepare(on database: any Database) async throws {
      if let sql = database as? SQLDatabase {
          // 修改解码方式
        let albumWordCountField = try await sql.raw("""
                SELECT COUNT(*) as count
                FROM information_schema.columns
                WHERE table_name = '\(unsafeRaw: Album.schema)'
                AND column_name = 'wordCount'
            """).first(decoding: CountResult.self)?.count ?? 0
        
        if albumWordCountField == 0 {
          try await database.schema(Album.schema)
            .field(Album.FieldKeys.wordCount, .int, .required, .sql(.default(0)))
            .update()
          LogManager.shared.debug("将wordCount 字段添加到 albums 表", format: .standard)
        } else {
          LogManager.shared.debug("wordCount 字段已经存在", format: .standard)
        }
        
          // 对 Transcript 表做同样的修改
        let transcriptWordCountField = try await sql.raw("""
                SELECT COUNT(*) as count
                FROM information_schema.columns
                WHERE table_name = '\(unsafeRaw: Transcript.schema)'
                AND column_name = 'wordCount'
            """).first(decoding: CountResult.self)?.count ?? 0
        
        if transcriptWordCountField == 0 {
          try await database.schema(Transcript.schema)
            .field(
              Transcript.FieldKeys.wordCount,
              .int,
              .required,
              .sql(.default(0))
            )
            .update()
          LogManager.shared.debug("将 wordCount 字段添加到 transcript 表", format: .standard)
        } else {
          LogManager.shared.debug("wordCount 字段已经存在了", format: .standard)
        }
      }
      
      try await processHistoricalData(on: database)
    }
    
    func revert(on database: any Database) async throws {
      try await database.schema(Album.schema)
        .deleteField(Album.FieldKeys.wordCount)
        .update()
      try await database.schema(Transcript.schema)
        .deleteField(Transcript.FieldKeys.wordCount)
        .update()
    }
    func processHistoricalData(on database: Database) async throws {
      let batchSize = 100
      var offset = 0
      
      while true {
        let transcripts = try await Transcript.query(on: database)
          .with(\.$audio) { audio in
            audio.with(\.$album)
          }
          .limit(batchSize)
          .offset(offset)
          .all()
        
        if transcripts.isEmpty {
          break
        }
        
          // 使用字典来累积每个专辑的单词数
        var albumWordCounts: [UUID: Int] = [:]
        
          // 批量更新 transcripts
        for transcript in transcripts {
          let wordCount = ArabicWordCounter.countWords(transcript.sentence)
          transcript.wordCount = wordCount
          
          if let albumId = transcript.audio.album.id {
            albumWordCounts[albumId, default: 0] += wordCount
          }
          
            // 单独更新每个 transcript
          try await transcript.update(on: database)
        }
        
          // 更新专辑的单词数
        for (albumId, count) in albumWordCounts {
          try await Album.query(on: database)
            .filter(\.$id == albumId)
            .set(\.$wordCount, to: count)
            .update()
        }
        
        offset += batchSize
      }
    }
  }
}
